--加盟信息表
create table join_information(
  store_corp varchar2(20),  --/法人
  corp_gender varchar2(2),
  corp_birth date,
  corp_idcard varchar2(20),
  corp_phone int,
  corp_address varchar2(50),
  sales_person varchar2(20),
  join_date date
);

--加盟店信息
create table store_info(
  store_id int,
  store_corp varchar2(20),  --/法人
  store_name varchar2(20),
  store_credentials varchar2(100),  --/证件
  detailed_loc varchar2(100),
  store_area number(7,2),
  open_start date
);

--地域纬度
create table region_info(
  detailed_loc varchar2(100),
  nation_loc varchar2(50),
  province_loc varchar2(50),
  city_loc varchar2(50),
  region_loc varchar2(50)
);

--供应商信息
create table store_info(
  fact_id int,
  fact_name varchar2(20),  
  fact_loc varchar2(50),
  phone int
);

--商品信息表
create table goods_info(
  goods_id int ,
  goods_name varchar2(20),
  goods_category varchar2(20), --/商品类型
  goods_brand varchar2(50),
  goods_price number(7,2),
  goods_prodate date,         --/生产日期
  goods_expgdate date ,        -- /过期日期
  goods_fact varchar2(15) , --/厂商
  goods_pro_loc varchar2(50) , --/生产地
  goods_brief varchar(500)  --/商品简介
);

--进货表
create table stock_info(
  goods_id int ,
  goods_num number(7,2) ,
  goods_stock date,         --/进货日期
  stock_price number(7,2),
  remark varchar(500),    --/备注
  s_order_id int
);

--出货表
create table clear_info(
  store_id int,
  goods_id int,
  goods_num number(7,2),
  goods_clear date,         --/出货日期
  clear_price number(7,2),
  remark varchar(500),    --/备注
  fact_id int,
  c_order_id int
);

--销售信息表
create table sale_info(
  store_id int,
  goods_id int,
  goods_num number(7,2),
  date_key date,         --/订单日期
  sale_price number(7,2),
  order_id int
);

--时间纬度
create table date_info(
  date_key date,        
  date_year date,
  date_month date,
  date_day date,
  date_week date,
  week_year date,
  quarter date
);

create sequence seq_id 
increment by 1 
start with 1
maxvalue 999999 
cycle 
nocache;

INSERT INTO goods_info VALUES(seq_goods.nextval,'雪碧小','饮料类','可口可乐',2.5,date'2020-05-08',date'2021-05-08','长沙可口可乐有限公司','好喝');
INSERT INTO goods_info VALUES(seq_goods.nextval,'雪碧大','饮料类','可口可乐',4,date'2020-05-08',date'2021-05-08','长沙可口可乐有限公司','好喝');
INSERT INTO goods_info VALUES(seq_goods.nextval,'冰红茶','饮料类','康师傅',2.5,date'2020-05-08',date'2021-05-08','康师傅有限公司','好喝');
INSERT INTO goods_info VALUES(seq_goods.nextval,'铁观音','饮料类','茶道世家',10,date'2020-05-08',date'2021-05-08','茶道世家有限公司','好喝');
INSERT INTO goods_info VALUES(seq_goods.nextval,'菊花茶','饮料类','可口可乐',2.5,date'2020-05-08',date'2021-05-08','长沙可口可乐有限公司','好喝');
INSERT INTO goods_info VALUES(seq_goods.nextval,'百威酒','烟酒类','百威',6,date'2020-05-08',date'2021-05-08','青岛百威有限公司','够劲');
INSERT INTO goods_info VALUES(seq_goods.nextval,'康师傅方便面','速食类','康师傅',3.4,date'2020-05-08',date'2021-05-08','康师傅有限公司','好吃');
INSERT INTO goods_info VALUES(seq_goods.nextval,'德芙巧克力','甜品类','德芙',5,date'2020-05-08',date'2021-05-08','德芙有限公司','好恰');
INSERT INTO goods_info VALUES(seq_goods.nextval,'真巧巧克力饼干','甜品类','真巧',5,date'2020-05-08',date'2021-05-08','真巧有限公司','好恰');
INSERT INTO goods_info VALUES(seq_goods.nextval,'奥利奥饼干','甜品类','奥利奥',5,date'2020-05-08',date'2021-05-08','奥利奥有限公司','好恰');


INSERT INTO goods_info VALUES(seq_goods.nextval,'雪碧小','饮料类','可口可乐',2.5,date'2021-05-08',date'2022-05-08');
INSERT INTO goods_info VALUES(seq_goods.nextval,'雪碧大','饮料类','可口可乐',4,date'2021-05-08',date'2022-05-08');
INSERT INTO goods_info VALUES(seq_goods.nextval,'冰红茶','饮料类','康师傅',2.5,date'2021-05-08',date'2022-05-08');
INSERT INTO goods_info VALUES(seq_goods.nextval,'铁观音','饮料类','茶道世家',10,date'2021-05-08',date'2022-05-08');
INSERT INTO goods_info VALUES(seq_goods.nextval,'菊花茶','饮料类','可口可乐',2.5,date'2021-05-08',date'2022-05-08');
INSERT INTO goods_info VALUES(seq_goods.nextval,'百威酒','酒类','百威',6,date'2021-05-08',date'2022-05-08');
INSERT INTO goods_info VALUES(seq_goods.nextval,'康师傅方便面','速食类','康师傅',3.4,date'2021-05-08',date'2022-05-08');
INSERT INTO goods_info VALUES(seq_goods.nextval,'德芙巧克力','甜品类','德芙',5,date'2021-05-08',date'2022-05-08');
INSERT INTO goods_info VALUES(seq_goods.nextval,'真巧巧克力饼干','甜品类','真巧',5,date'2021-05-08',date'2022-05-08');
INSERT INTO goods_info VALUES(seq_goods.nextval,'奥利奥饼干','甜品类','奥利奥',5,date'2021-05-08',date'2022-05-08');
--
insert into goods_info values(seq_goods.nextval,'麻辣鸭脖','肉类','鸭霸王',5,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'香辣牛肉干','肉类','真牛',8,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'芒果干','果干类','好果味',3,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'草莓干','果干类','好果味',4,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'鱼','肉类','鸭霸王',2.5,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'蔬果干大礼包','果干类','好果味',8,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'香瓜子','坚果类','坚果味',3.5,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'光明酸奶','乳液','光明',5,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'风干甜辣牛肉','肉类','鸭霸王',9,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'香辣鸡爪','肉类','鸭霸王',5,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'坚果大礼包','坚果类','坚果味',12,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'伊利牛奶','乳液','伊利',2.5,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'百事可乐','饮料类','可口可乐',2.5,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'达利园小面包','面包','达利园',2,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'火鸡面','速食类','康师傅',3,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'多口味薄荷糖','甜品类','绿箭',5,date'2021-10-10',date'2022-10-10');
insert into goods_info values(seq_goods.nextval,'雪花啤酒','酒类','青岛',3,date'2021-10-10',date'2022-10-10');